<?php
/**
* BRail Application Database Test Case
*/
class BRail_Application_DatabaseTestCase extends PHPUnit_Framework_TestCase
{
    /**
    * @var Zend_Db_Adapter_Pdo_Mysql
    */
    public $db;

    /**
    * @var array
    */
    public $reservedKeywords = array(
        'accessible','add','all','alter','analyze','and','as','asc','asensitive','before','between',
        'bigint','binary','blob','both','by','call','cascade','case','change','char','character',
        'check','collate','column','condition','connection','constraint','continue','convert',
        'create','cross','current_date','current_time','current_timestamp','current_user','cursor',
        'database','databases','day_hour','day_microsecond','day_minute','day_second','dec',
        'decimal','declare','default','delayed','delete','desc','describe','deterministic',
        'distinct','distinctrow','div','double','drop','dual','each','else','elseif','enclosed',
        'escaped','exists','exit','explain','false','fetch','float','float4','float8','for','force',
        'foreign','from','fulltext','goto','grant','group','having','high_priority',
        'hour_microsecond','hour_minute','hour_second','if','ignore','in','index','infile','inner',
        'inout','insensitive','insert','int','int1','int2','int3','int4','int8','integer',
        'interval','into','is','iterate','join','key','keys','kill','label','leading','leave',
        'left','like','limit','linear','lines','load','localtime','localtimestamp','lock','long',
        'longblob','longtext','loop','low_priority','master_ssl_verify_server_cert','match',
        'mediumblob','mediumint','mediumtext','middleint','minute_microsecond','minute_second',
        'mod','modifies','natural','not','no_write_to_binlog','null','numeric','on','optimize',
        'option','optionally','or','order','out','outer','outfile','precision','primary',
        'procedure','purge','range','read','reads','read_only','read_write','real','references',
        'regexp','release','rename','repeat','replace','require','restrict','return','revoke',
        'right','rlike','schema','schemas','second_microsecond','select','sensitive','separator',
        'set','show','smallint','spatial','specific','sql','sqlexception','sqlstate','sqlwarning',
        'sql_big_result','sql_calc_found_rows','sql_small_result','ssl','starting','straight_join',
        'table','terminated','then','tinyblob','tinyint','tinytext','to','trailing','trigger',
        'true','undo','union','unique','unlock','unsigned','update','upgrade','usage','use','using',
        'utc_date','utc_time','utc_timestamp','values','varbinary','varchar','varcharacter',
        'varying','when','where','while','with','write','xor','year_month','zerofill'
    );

    /**
    * Create the database link used in each test
    * @return void
    */
    public function setUp()
    {
        $application = Zend_Registry::get('Zend_Application');
        $application->bootstrap();
        $this->db = $application->getBootstrap()->getResource('db');
    }

    /**
    * Checks tablenames naming convention
    */
    public function testTablenamesConvention()
    {
        foreach ($this->db->listTables() as $tablename) {
            $this->assertRegExp('/^[a-z]{1}[0-9]{3}_[a-z_]{1,63}$/', $tablename);
        }
    }

    /**
    * Checks tablenames against reserved keywords
    */
    public function testTablenamesReservedKeywords()
    {
        foreach ($this->db->listTables() as $tablename) {
            foreach ($this->reservedKeywords as $keyword) {
                $this->assertNotContains($keyword, $tablename);
            }
        }
    }

    /**
    * Checks columnnames naming convention
    */
    public function testColumnnamesConvention()
    {
        foreach ($this->db->listTables() as $tablename) {
            foreach ($this->db->describeTable($tablename) as $columnname => $array) {
                $this->assertRegExp('/^[a-z_]{1,64}$/', $columnname);
            }
        }
    }

    /**
    * Checks primary keys columns naming convention
    */
    public function testPrimaryKeysConvention()
    {
        foreach ($this->db->listTables() as $tablename) {
            foreach ($this->db->describeTable($tablename) as $columnname => $array) {
                if ($array['PRIMARY'] === true) {
                    $this->assertRegExp('/^[a-z_]{1,61}_id$/', $columnname);
                }
            }
        }
    }

    /**
    * Checks date columns naming convention
    */
    public function testDatesConvention()
    {
        $dateTypes = array('date', 'time', 'datetime');
        foreach ($this->db->listTables() as $tablename) {
            foreach ($this->db->describeTable($tablename) as $columnname => $array) {
                if (in_array($array['DATA_TYPE'], $dateTypes)) {
                    $this->assertRegExp('/^[a-z_]{1,59}_date$/', $columnname);
                }
            }
        }
    }
}